﻿/*
   Open Intel 
   Copyright © 2011 – ISC. All Rights Reserved.
*/

using System;
using System.IO;
using System.Linq;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Text;
using System.Threading;
using Ionic.Zip;
using ISC.MapDotNetServer.Common;
using ISC.MapDotNetServer.Common.Data;
using ISC.MapDotNetServer.Common.Data.Interrogation;
using ISC.MapDotNetServer.Core.Data.ShapefileSupport;
using ISC.MapDotNetServer.Core.Services;
using Microsoft.WindowsAzure.ServiceRuntime;
using Path = System.IO.Path;
using System.Collections.Generic;

namespace OI.Web.DataServices
{
    /// <summary>
    /// Service for table extracts.  Tables should have integer primary keys no larger than 4 bytes.
    /// </summary>
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class DataPackagingService
    {
        private const string WKT4326 =
            "GEOGCS[\"WGS 84\",DATUM[\"WGS_1984\",SPHEROID[\"WGS 84\",6378137,298.257223563,AUTHORITY[\"EPSG\",\"7030\"]],AUTHORITY[\"EPSG\",\"6326\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4326\"]]";

        private const string ReadMeFormat = "Shapefile created using MapDotNet UX.\r\n\r\n" +
                                            "MapDotNet UX copyright (C) 2010 - Specialized Technology Products, Inc. All Rights Reserved.\r\n\r\n" +
                                            "This shapefile can be used by any software that recognizes shapefiles, however, the {0}.mdns.proj and {0}.mdns.index files are specific to MapDotNet UX.\r\n\r\n" +
                                            "See www.mapdotnet.com to learn more about MapDotNet and download free trial and Express versions of our map web services, visualization tools, and SDKs for Rich Interactive Mapping.";

        /// <summary>
        /// Hidden member supporting the StorageFactory property.
        /// </summary>
        private static NTFSStorageFactory _StorageFactory;

        /// <summary>
        /// Lock object for accessing _StorageFactory, used in the StorageFactory property.
        /// </summary>
        private static readonly object _StorageFactoryLockObject = new object();

        internal static string OIConnectionString
        {
            get { return Global.OIConnectionString; }
        }

        /// <summary>
        /// Gets the storage factory, a singleton for this class for creating BLOB storage objects and containers.
        /// </summary>
        /// <value>The storage factory.</value>
        private static NTFSStorageFactory StorageFactory
        {
            get
            {
                lock (_StorageFactoryLockObject)
                {
                    if (_StorageFactory == null)
                    {
                        _StorageFactory = new NTFSStorageFactory();
                        _StorageFactory.InitializeStorage(
                            ResourceManager.GetNameValueSection("mapdotnet.adminservicesettings"));
                    }
                }
                return _StorageFactory;
            }
        }


        /// <summary>
        /// Creates a CSV file with table data.  Geometry is omitted for spatial tables.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <returns></returns>
        [OperationContract]
        public DataPackagingResult CreateCSV(string tableName)
        {
            var result = new DataPackagingResult();

            try
            {
                SpatialTableDescription desc = GetTableDescription(tableName);

                if (desc.Extents != null)
                {
                    // clear off the spatial info before calling GetData
                    desc.GeometryColumnName = null;
                    desc.Extents = null;
                    desc.GeometryType = GeometryTypes.UNINITIALIZED;
                }

                // storage object
                string key = Guid.NewGuid().ToString("N") + ".csv";
                IStorageObject storage = CreateStorageObject(key);

                // create a data iterator that batches under the hood
                var tableReader = new TableDataReader(desc);

                // read rows
                QueryResultTable table;
                bool hasHeader = false;
                using (var writer = new StreamWriter(storage.OpenWrite()))
                {
                    while ((table = tableReader.GetNextDataBlock()) != null)
                    {
                        if (!hasHeader)
                        {
                            for (int f = 0; f < table.Fields.Count; f++)
                            {
                                if (f > 0)
                                {
                                    writer.Write(',');
                                }
                                writer.Write(table.Fields[f]);
                            }
                            writer.WriteLine();
                            hasHeader = true;
                        }

                        foreach (QueryResultRow row in table.QueryResultRows)
                        {
                            for (int f = 0; f < table.Fields.Count; f++)
                            {
                                if (f > 0)
                                {
                                    writer.Write(',');
                                }

                                writer.Write('"');
                                writer.Write(row.Values[f].Replace("\"", "\"\""));
                                writer.Write('"');
                            }
                            writer.WriteLine();
                        }
                    }
                    writer.Flush();
                }

                // get bytes
                result.Data = GetData(storage);

                // cleanup
                storage.Delete();
            }
            catch (Exception ex)
            {
                result.Data = null;
                result.ErrorMessage = ex.Message;
            }

            return result;
        }


        /// <summary>
        /// Creates a shapefile with table data for spatial tables..
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <returns></returns>
        [OperationContract]
        public DataPackagingResult CreateShapefile(string tableName)
        {
            var result = new DataPackagingResult();

            try
            {
                SpatialTableDescription desc = GetTableDescription(tableName);

                if (desc.Extents != null)
                {
                    var tableReader = new TableDataReader(desc);

                    string key = Guid.NewGuid().ToString("N");
                    IStorageContainer container = CreateStorageContainer();
                    var connector = new ShapefileConnector(container);

                    desc.ClearAttributeDBTypeNames();

                    desc.Name = key;

                    // OK to change this after calling new TableDataReader(desc);, connector.CreateSpatialTable uses this for the mdns.proj file
                    desc.CoordinateReference = new ProjCoordSys
                                                   {ID = 4326, CSString = EPSGProjection.GetByID(4326).Text};

                    SpatialTableOperationResult stor = null;

                    // depending on the database user permission level we may not have been able to determine the geometry type
                    // GetTableDescription tries to infer it from check constraints
                    if (desc.GeometryType != GeometryTypes.INDETERMINATE)
                    {
                        stor = connector.CreateSpatialTable(desc, null);
                        if (!stor.IsSuccess)
                        {
                            throw new ApplicationException(stor.ErrorMessage);
                        }
                    }

                    QueryResultTable table;

                    Dictionary<string, string> columnsRenamed = new Dictionary<string, string>();

                    while ((table = tableReader.GetNextDataBlock()) != null)
                    {
                        if (stor == null)
                        {
                            // try to infer the geometry type from the actual data
                            desc.GeometryType = InferGeometryType(table);
                            if (desc.GeometryType != GeometryTypes.INDETERMINATE)
                            {
                                stor = connector.CreateSpatialTable(desc, null);
                                if (!stor.IsSuccess)
                                {
                                    throw new ApplicationException(stor.ErrorMessage);
                                }
                                if (stor.RenamedAttributes != null)
                                {
                                    for (int n = 0; n + 1 < stor.RenamedAttributes.Count; n += 2)
                                    {
                                        columnsRenamed.Add(stor.RenamedAttributes[n], stor.RenamedAttributes[n + 1]);
                                    }
                                }
                            }
                        }

                        if (stor != null)
                        {

                            var ereq = new EditRequest
                                           {
                                               DataSourceSpecification = new ConnectionDataSourceSpecification
                                                                             {
                                                                                 DataTable = desc.Name + ".shp",
                                                                                 ConnectionType =
                                                                                     ConnectionDataSourceSpecification.
                                                                                     ConnectionTypes.Shapefile
                                                                             },
                                               Data = table.GetEditDataTable(desc.GeometryColumnName)
                                           };

                            foreach (string origName in columnsRenamed.Keys)
                            {
                                foreach (ISC.MapDotNetServer.Common.Data.EditDataModel.DataColumn dc in ereq.Data.Columns)
                                {
                                    if (dc.ColumnName == origName)
                                    {
                                        dc.ColumnName = columnsRenamed[origName];
                                    }
                                }
                            }

                            int index = 0;

                            connector.Edit(ereq, ref index);
                        }
                    }

                    ShapefileHelper.IndexShapefile(container.CreateObjectReference(desc.Name + ".shp"), 7);

                    var zipfile = new ZipFile();

                    foreach (IStorageObject obj in container.GetObjects())
                    {
                        if (obj.Name.StartsWith(key))
                        {
                            if (obj.Name.Contains(".mdns."))
                            {
                                zipfile.AddEntry(tableName + obj.Name.Substring(obj.Name.LastIndexOf(".mdns.")),
                                                 obj.OpenRead());
                            }
                            else
                            {
                                zipfile.AddEntry(tableName + obj.Name.Substring(obj.Name.LastIndexOf('.')),
                                                 obj.OpenRead());
                            }
                            // obj.Delete();
                        }
                    }

                    zipfile.AddEntry(tableName + ".prj", WKT4326, Encoding.ASCII);

                    zipfile.AddEntry("ReadMe-mdns.txt", string.Format(ReadMeFormat, tableName), Encoding.ASCII);

                    IStorageObject zipObject = CreateStorageObject(key + ".zip");

                    using (Stream writer = zipObject.OpenWrite())
                    {
                        zipfile.Save(writer);
                        writer.Flush();
                    }

                    // get bytes
                    result.Data = GetData(zipObject);

                    // cleanup
                    foreach (IStorageObject obj in container.GetObjects())
                    {
                        if (obj.Name.StartsWith(key))
                        {
                            obj.Delete();
                        }
                    }
                }
                else
                {
                    throw new ApplicationException(
                        "CreateShapefile is only used for spatial tables: You must use CreateCSV to package other tables.");
                }
            }
            catch (Exception ex)
            {
                result.Data = null;
                result.ErrorMessage = ex.Message;
            }

            return result;
        }


        // gets the storage object as a byte[]
        private byte[] GetData(IStorageObject storage)
        {
            byte[] ret;
            int size = 8096;
            var buffer = new byte[size];
            using (var ms = new MemoryStream())
            {
                using (var reader = storage.OpenRead())
                {
                    int remainder = (int)reader.Length;
                    int fetched = 0;

                    if (remainder < size)
                    {
                        size = remainder;
                    }

                    while (size > 0 && (fetched = reader.Read(buffer, 0, size)) > 0)
                    {
                        ms.Write(buffer, 0, fetched);
                        remainder -= fetched;

                        if (remainder < size)
                        {
                            size = remainder;
                        }
                    }

                }
                ret = ms.ToArray();
            }
            return ret;
        }


        /// <summary>
        /// Infers the type of the geometry based on data.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <returns></returns>
        private static GeometryTypes InferGeometryType(QueryResultTable table)
        {
            foreach (QueryResultRow row in table.QueryResultRows)
            {
                if (row.Shape != null)
                {
                    GeometryTypes type = row.Shape.GetGeometryType();
                    return type;
                }
            }
            return GeometryTypes.INDETERMINATE;
        }

        /// <summary>
        /// Creates a working subfolder
        /// </summary>
        public static IStorageContainer CreateStorageContainer()
        {
            IStorageContainer container = StorageFactory.CreateContainerStorage(GetLocalPath());
            return container;
        }


        /// <summary>
        /// Creates a new object in storage
        /// </summary>
        /// <param name="name">The name.</param>
        public static IStorageObject CreateStorageObject(string name)
        {
            string path = GetLocalPath() + "\\" + name;
            return StorageFactory.CreateObjectStorage(path);
        }


        // get existing storage object
        public static IStorageObject GetStorageObject(string path)
        {
            return StorageFactory.CreateObjectStorage(path);
        }


        private static string GetLocalPath()
        {
            string localPath = "";
            if (RoleEnvironment.IsAvailable)
            {
                var lr = RoleEnvironment.GetLocalResource("DriveCache");
                if (lr != null)
                {
                    localPath = lr.RootPath;
                }
            }
            
            if (localPath.Length == 0)
            {
                localPath = Path.GetTempPath();
            }
            return localPath.Trim('\\');
        }


        /// <summary>
        /// Gets the table description.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <returns></returns>
        private static SpatialTableDescription GetTableDescription(string tableName)
        {
            SpatialTableDescription desc = SpatialTableDescription.Deserialize(
                ResourceManager.AdminServiceSingle().GetSpatialTableDescription(
                    ConnectionDataSourceSpecification.ConnectionTypes.SqlServer2008,
                    OIConnectionString,
                    tableName
                    ));

            if (!string.IsNullOrEmpty(desc.Error))
            {
                throw new ApplicationException(desc.Error);
            }

            return desc;
        }

        #region Nested type: DataPackagingResult

        public class DataPackagingResult
        {
            public string ErrorMessage { get; set; }
            public byte[] Data { get; set; }
        }

        #endregion

        #region Nested type: TableDataReader

        /// <summary>
        /// A class to support reading table data in chunks.
        /// </summary>
        private class TableDataReader
        {
            /// <summary>
            /// Max PK value.
            /// </summary>
            private readonly long Max;

            /// <summary>
            /// Min PK value.
            /// </summary>
            private readonly long Min;

            /// <summary>
            /// Table primary key column.
            /// </summary>
            private readonly string PK;

            /// <summary>
            /// QueryRequest object used for searches.
            /// </summary>
            private readonly QueryRequest QueryRequest;

            /// <summary>
            /// Amount ti increment PK search ranges by.
            /// </summary>
            private readonly long Step;

            /// <summary>
            /// The max of the current search range, or CurrentMax LT CurrentMin if searches have not started.
            /// </summary>
            private long CurrentMax;

            /// <summary>
            /// The min of the current search range, or CurrentMin GT CurrentMax if searches have not started.
            /// </summary>
            private long CurrentMin = 1;

            /// <summary>
            /// Initializes a new instance of the <see cref="TableDataReader"/> class.
            /// </summary>
            /// <param name="desc">The desc.</param>
            internal TableDataReader(SpatialTableDescription desc)
            {
                // get min and max PK values
                PK = (from attr in desc.Attributes where attr.IsPrimaryKey select attr.Name).FirstOrDefault();

                if (string.IsNullOrEmpty(PK))
                {
                    throw new ApplicationException("Table must have as primary key.");
                }

                var qr = new QueryRequest();
                qr.ReturnShapes = false;

                var query = new Query();
                query.Fields.Add(PK);
                query.DataSelectionKeyword = Query.DataSelectionKeywords.Min;
                query.DataSourceSpecification = new ConnectionDataSourceSpecification
                                                    {
                                                        Connection = OIConnectionString,
                                                        ConnectionType =
                                                            ConnectionDataSourceSpecification.ConnectionTypes.
                                                            SqlServer2008,
                                                        DataTable = desc.Name,
                                                        GeometryColumn = desc.GeometryColumnName,
                                                        Type = desc.GeometryType,
                                                        Projection =
                                                            (desc.Extents == null
                                                                 ? null
                                                                 : new ProjCoordSys
                                                                       {
                                                                           ID = 3857,
                                                                           CSString = EPSGProjection.GetByID(3857).Text
                                                                       })
                                                    };

                qr.Queries.Add(query);

                query = new Query();
                query.Fields.Add(PK);
                query.DataSelectionKeyword = Query.DataSelectionKeywords.Max;
                query.DataSourceSpecification = qr.Queries[0].DataSourceSpecification;

                qr.Queries.Add(query);

                var handle = new EventWaitHandle(false, EventResetMode.ManualReset);

                QueryResponse response = null;

                ResourceManager.FeatureServiceSingle().BeginQuery(
                    qr.Serialize(false),
                    Guid.NewGuid(),
                    (ar) =>
                        {
                            response = QueryResponse.Deserialize(ResourceManager.FeatureServiceSingle().EndQuery(ar));
                            handle.Set();
                        },
                    null
                    );

                handle.WaitOne();

                if (!string.IsNullOrEmpty(response.Error))
                {
                    throw new ApplicationException(response.Error);
                }

                if (!string.IsNullOrEmpty(response.QueryResultTables[0].Error))
                {
                    throw new ApplicationException(response.QueryResultTables[0].Error);
                }

                try
                {
                    Min = long.Parse(response.QueryResultTables[0].QueryResultRows[0].Values[0]);
                    Max = long.Parse(response.QueryResultTables[1].QueryResultRows[0].Values[0]);

                    // step to get approx 1000 records at a time
                    Step = (long) (((Max - Min)/(double) desc.FeatureCount)*1000.0);
                }
                catch
                {
                    throw new ApplicationException("Unable to parse primary key values as integers.");
                }


                // create a query request for further use

                qr = new QueryRequest();
                qr.ReturnTypes = true;
                if (desc.Extents == null)
                {
                    qr.ReturnShapes = false;
                }
                else
                {
                    qr.ReturnShapes = true;
                    qr.ResultShapeCoordSys = new ProjCoordSys {ID = 4326, CSString = EPSGProjection.GetByID(4326).Text};
                }

                query = new Query();
                foreach (DataAttribute attr in desc.Attributes)
                {
                    if (!attr.IsGeometry)
                    {
                        query.Fields.Add(attr.Name);
                    }
                }
                query.DataSourceSpecification = new ConnectionDataSourceSpecification
                                                    {
                                                        Connection = OIConnectionString,
                                                        ConnectionType =
                                                            ConnectionDataSourceSpecification.ConnectionTypes.
                                                            SqlServer2008,
                                                        DataTable = desc.Name,
                                                        GeometryColumn = desc.GeometryColumnName,
                                                        Type = desc.GeometryType,
                                                        Projection =
                                                            (desc.Extents == null
                                                                 ? null
                                                                 : new ProjCoordSys
                                                                       {
                                                                           ID = 3857,
                                                                           CSString = EPSGProjection.GetByID(3857).Text
                                                                       })
                                                    };

                qr.Queries.Add(query);

                QueryRequest = qr;
            }

            /// <summary>
            /// Gets the next data block, or null if the table has been read completely.
            /// </summary>
            /// <param name="desc">The desc.</param>
            /// <returns></returns>
            internal QueryResultTable GetNextDataBlock()
            {
                Query query = QueryRequest.Queries[0];

                if (CurrentMin > CurrentMax)
                {
                    CurrentMin = Min;
                }
                else if (CurrentMax < Max)
                {
                    CurrentMin = CurrentMax + 1;
                }
                else
                {
                    // done
                    return null;
                }

                CurrentMax = CurrentMin + Step;
                query.Filter = PK + " >= " + CurrentMin + " AND " + PK + " <= " + CurrentMax;

                var handle = new EventWaitHandle(false, EventResetMode.ManualReset);

                QueryResponse response = null;

                ResourceManager.FeatureServiceSingle().BeginQuery(
                    QueryRequest.Serialize(false),
                    Guid.NewGuid(),
                    (ar) =>
                        {
                            response = QueryResponse.Deserialize(ResourceManager.FeatureServiceSingle().EndQuery(ar));
                            handle.Set();
                        },
                    null
                    );

                handle.WaitOne();

                if (!string.IsNullOrEmpty(response.Error))
                {
                    throw new ApplicationException(response.Error);
                }

                if (!string.IsNullOrEmpty(response.QueryResultTables[0].Error))
                {
                    throw new ApplicationException(response.QueryResultTables[0].Error);
                }

                return response.QueryResultTables[0];
            }
        }

        #endregion
    }
}